package com.ruoyi.tongji.run;

import com.ruoyi.tongji.entity.宣传员;
import com.ruoyi.tongji.entity.宣传员费用登记表;
import com.ruoyi.tongji.entity.路费差额统计;
import com.ruoyi.tongji.run.manager.ManagerFactory;
import com.ruoyi.tongji.utils.DateUtil;
import com.ruoyi.tongji.utils.JxlsUtils;
import com.ruoyi.tongji.utils.PlatformException;
import org.apache.commons.io.FileUtils;
import org.beetl.sql.core.*;
import org.beetl.sql.core.db.DBStyle;
import org.beetl.sql.core.db.MySqlStyle;
import org.beetl.sql.ext.DebugInterceptor;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.*;

public class LufeiTongjiBeetl {

    public static void main(String[] args) {
        SQLManager sqlManager = ManagerFactory.jiangzhanSqlManager();

        Date dateRangeStart = DateUtil.getDate("2021-08-23");
        Date dateRangeEnd = DateUtil.getDate("2021-08-29");
        String dateRange = DateUtil.format(dateRangeStart, "yyyy年MM月dd日") + "至" + DateUtil.format(dateRangeEnd, "yyyy年MM月dd日");

        String[] xcyType = new String[] {"正常宣传员","新宣传员"};

        for (String type : xcyType) {
            String root = "E:\\文档\\第三方费用统计\\" + dateRange;
            File rootFile = new File(root);
            rootFile.mkdirs();
            //if(true) return ;
            宣传员 xcy = new 宣传员();
            xcy.setType(type);
            List xcyList = sqlManager.template(xcy);
            List xcyHuizongList = new ArrayList();

            Double jianyiqiTotal = 0D;
            Double chaobannianTotal = 0D;
            Double buchongjieshaofeiTotal = 0D;
            Double qitafeiyongTotal = 0D;
            Double lufeihejiTotal = 0D;

            for (Iterator<宣传员> iterator = xcyList.iterator(); iterator.hasNext();) {
                Map xcyHuizong = new HashMap();

                宣传员 x = iterator.next();

                //未有合并的宣传员
                if (x.getHebingName() == null) {
                    String name = x.getName();
                    Map<String, Object> model = new HashMap<>();
                    model.put("dateRange",dateRange);

                    Double jianyiqi = 0D;
                    Double chaobannian = 0D;
                    Double buchongjieshaofei = 0D;
                    Double qitafeiyong = 0D;
                    Double lufeiheji = 0D;

                    List<路费差额统计> lufeiList = sqlManager.execute(new SQLReady("select * from (select 路费区域,count(*) as count,差额,sum(差额) as 管理费合计 from 路费差额统计 where 宣传员姓名 = ? group by 路费区域,差额) as t order by t.count desc ", new Object[] {name}),路费差额统计.class);
                    List<路费差额统计> lufeiHejiList = sqlManager.execute(new SQLReady("select sum(差额) as 管理费合计 from 路费差额统计 where 宣传员姓名 = ? ", new Object[] {name}),路费差额统计.class);
                    List<路费差额统计> lufeiZongshu = sqlManager.execute(new SQLReady("select count(*) as count from 路费差额统计 where 宣传员姓名 = ? ", new Object[] {name}),路费差额统计.class);
                    if (lufeiHejiList != null && lufeiHejiList.size() == 1) {
                        model.put("lufeiheji", lufeiHejiList.get(0).get管理费合计());
                        if (lufeiHejiList.get(0).get管理费合计() != null) {
                            lufeiheji = lufeiHejiList.get(0).get管理费合计();
                        }
                    }
                    if (lufeiZongshu.get(0).getCount() != 0) {
                        model.put("lufeiZongshu", lufeiZongshu.get(0).getCount());
                    }

                    //name 正常宣传员费用统计,name1为空的宣传员
                    try {
                        List<宣传员费用登记表> 检疫期费用 = sqlManager.execute(new SQLReady("select 金额,count(*) as count,sum(金额) as 总金额 from 宣传员费用登记表 where 领取人 = ? and 金额=30 and 日期 between ? and ? group by 金额 ", new Object[] {name,dateRangeStart, dateRangeEnd}), 宣传员费用登记表.class);
                        if (检疫期费用 != null && 检疫期费用.size() == 1) {

                            model.put("jianyiqi",检疫期费用.get(0));
                            if (检疫期费用.get(0).get总金额() != null) {
                                jianyiqi = 检疫期费用.get(0).get总金额();
                            }
                        } else if (检疫期费用.size() > 1) {
                            File f = new File(root + "\\宣传员\\error.txt");
                            FileUtils.write(f,name + "检疫期费用出错|",true);
                        }

                        List<宣传员费用登记表> 超半年费用 = sqlManager.execute(new SQLReady("select 金额,count(*) as count,sum(金额) as 总金额 from 宣传员费用登记表 where 领取人 = ? and 金额=50 and 日期 between ? and ? group by 金额 ", new Object[] {name,dateRangeStart, dateRangeEnd}),宣传员费用登记表.class);
                        if (超半年费用 != null && 超半年费用.size() == 1) {
                            model.put("chaobannian",超半年费用.get(0));
                            if (超半年费用.get(0).get总金额() != null) {
                                chaobannian = 超半年费用.get(0).get总金额();
                            }
                        } else if (超半年费用.size() > 1) {
                            File f = new File(root + "\\宣传员\\error.txt");
                            FileUtils.write(f,name + "超半年费用出错|",true);
                        }

                        List<宣传员费用登记表> 补充介绍费 = sqlManager.execute(new SQLReady("select 金额,count(*) as count,sum(金额) as 总金额 from 宣传员费用登记表 where 领取人 = ? and 金额=40 and 日期 between ? and ? group by 金额 ", new Object[] {name,dateRangeStart, dateRangeEnd}),宣传员费用登记表.class);
                        if (补充介绍费 != null && 补充介绍费.size() == 1) {
                            model.put("buchongjieshaofei",补充介绍费.get(0));
                            if (补充介绍费.get(0).get总金额() != null) {
                                buchongjieshaofei = 补充介绍费.get(0).get总金额();
                            }
                        } else if (补充介绍费.size() > 1) {
                            File f = new File(root + "\\宣传员\\error.txt");
                            FileUtils.write(f,name + "补充介绍费出错|",true);
                        }
                        //该项主要针对王利曲堤5人补1人的政策
                        List<宣传员费用登记表> 其他费用 = sqlManager.execute(new SQLReady("select 金额,count(*) as count,sum(金额) as 总金额 from 宣传员费用登记表 where 领取人 = ? and (金额 !=30 and 金额!=40 and 金额!=50) and 日期 between ? and ? group by 金额 ", new Object[] {name,dateRangeStart, dateRangeEnd}),宣传员费用登记表.class);
                        if (其他费用 != null && 其他费用.size() == 1) {
                            model.put("qitafeiyong",其他费用.get(0));
                            if (其他费用.get(0).get总金额() != null) {
                                qitafeiyong = 其他费用.get(0).get总金额();
                            }
                        } else if (其他费用.size() > 1) {
                            File f = new File(root + "\\宣传员\\error.txt");
                            FileUtils.write(f,name + "其他费用|",true);
                        }


                        model.put("qitafeiyongheji",String.valueOf(jianyiqi + chaobannian + buchongjieshaofei + qitafeiyong));

                    } catch (IOException e) {
                        e.printStackTrace();
                    }

                    System.out.println("----------------");
                    String excelTemplate = "excelTemplates/管理费费用统计.xls";
                    try(InputStream is = Thread.currentThread().getContextClassLoader().getResourceAsStream(excelTemplate)) {
                        if(is==null) {
                            throw new PlatformException("模板资源不存在："+excelTemplate);
                        }
                        File f = new File(root + "\\宣传员\\" + name + ".xls");
                        f.getParentFile().mkdirs();

                        FileOutputStream os = new FileOutputStream(f);

                        model.put("lufeiList", lufeiList);
                        model.put("name",name);
                        model.put("nowdate", new Date());

                        JxlsUtils.exportExcel(is,os,model);
                    } catch (IOException e) {
                        throw new PlatformException(e.getMessage());
                    }

                    xcyHuizong.put("name",name);
                    xcyHuizong.put("jianyiqi",jianyiqi);jianyiqiTotal = jianyiqiTotal + jianyiqi;
                    xcyHuizong.put("chaobannian",chaobannian);chaobannianTotal = chaobannianTotal + chaobannian;
                    xcyHuizong.put("buchongjieshaofei",buchongjieshaofei);buchongjieshaofeiTotal = buchongjieshaofeiTotal + buchongjieshaofei;
                    xcyHuizong.put("qitafeiyong",qitafeiyong);qitafeiyongTotal = qitafeiyongTotal + qitafeiyong;
                    xcyHuizong.put("lufeiheji",lufeiheji);lufeihejiTotal = lufeihejiTotal + lufeiheji;

                    System.out.println(name);
                    xcyHuizong.put("heji",jianyiqi + chaobannian + buchongjieshaofei + lufeiheji + qitafeiyong);
                    xcyHuizongList.add(xcyHuizong);
                }


                if (x.getHebingName() != null) {
                    String name = x.getName();
                    Map<String, Object> model = new HashMap<>();
                    model.put("dateRange",dateRange);

                    Double jianyiqi = 0D;
                    Double chaobannian = 0D;
                    Double buchongjieshaofei = 0D;
                    Double qitafeiyong = 0D;
                    Double lufeiheji = 0D;

                    List<路费差额统计> lufeiList = sqlManager.execute(new SQLReady("select * from (select 路费区域,count(*) as count,差额,sum(差额) as 管理费合计 from 路费差额统计 where 宣传员姓名 = ? group by 路费区域,差额) as t order by t.count desc ", new Object[] {name}),路费差额统计.class);
                    List<路费差额统计> lufeiHejiList = sqlManager.execute(new SQLReady("select sum(差额) as 管理费合计 from 路费差额统计 where 宣传员姓名 = ? ", new Object[] {name}),路费差额统计.class);
                    List<路费差额统计> lufeiZongshu = sqlManager.execute(new SQLReady("select count(*) as count from 路费差额统计 where 宣传员姓名 = ? ", new Object[] {name}),路费差额统计.class);
                    if (lufeiHejiList != null && lufeiHejiList.size() == 1) {
                        model.put("lufeiheji", lufeiHejiList.get(0).get管理费合计());
                        if (lufeiHejiList.get(0).get管理费合计() != null) {
                            lufeiheji = lufeiHejiList.get(0).get管理费合计();
                        }
                    }
                    if (lufeiZongshu.get(0).getCount() != 0) {
                        model.put("lufeiZongshu", lufeiZongshu.get(0).getCount());
                    }

                    //name 正常宣传员费用统计,name1为空的宣传员
                    try {
                        List<宣传员费用登记表> 检疫期费用 = sqlManager.execute(new SQLReady("select 金额,count(*) as count,sum(金额) as 总金额 from 宣传员费用登记表 where 领取人 = ? and 金额=30 and 日期 between ? and ? group by 金额 ", new Object[] {name,dateRangeStart, dateRangeEnd}), 宣传员费用登记表.class);
                        if (检疫期费用 != null && 检疫期费用.size() == 1) {
                            model.put("jianyiqi",检疫期费用.get(0));
                            if (检疫期费用.get(0).get总金额() != null) {
                                jianyiqi = 检疫期费用.get(0).get总金额();
                            }
                        } else if (检疫期费用.size() > 1) {
                            File f = new File(root + "\\宣传员\\error.txt");
                            FileUtils.write(f,name + "检疫期费用出错|",true);
                        }

                        List<宣传员费用登记表> 超半年费用 = sqlManager.execute(new SQLReady("select 金额,count(*) as count,sum(金额) as 总金额 from 宣传员费用登记表 where 领取人 = ? and 金额=50 and 日期 between ? and ? group by 金额 ", new Object[] {name,dateRangeStart, dateRangeEnd}),宣传员费用登记表.class);
                        if (超半年费用 != null && 超半年费用.size() == 1) {
                            model.put("chaobannian",超半年费用.get(0));
                            if (超半年费用.get(0).get总金额() != null) {
                                chaobannian = 超半年费用.get(0).get总金额();
                            }
                        } else if (超半年费用.size() > 1) {
                            File f = new File(root + "\\宣传员\\error.txt");
                            FileUtils.write(f,name + "超半年费用出错|",true);
                        }

                        List<宣传员费用登记表> 补充介绍费 = sqlManager.execute(new SQLReady("select 金额,count(*) as count,sum(金额) as 总金额 from 宣传员费用登记表 where 领取人 = ? and 金额=40 and 日期 between ? and ? group by 金额 ", new Object[] {name,dateRangeStart, dateRangeEnd}),宣传员费用登记表.class);
                        if (补充介绍费 != null && 补充介绍费.size() == 1) {
                            model.put("buchongjieshaofei",补充介绍费.get(0));
                            if (补充介绍费.get(0).get总金额() != null) {
                                buchongjieshaofei = 补充介绍费.get(0).get总金额();
                            }
                        } else if (补充介绍费.size() > 1) {
                            File f = new File(root + "\\宣传员\\error.txt");
                            FileUtils.write(f,name + "补充介绍费出错|",true);
                        }
                        //该项主要针对王利曲堤5人补1人的政策
                        List<宣传员费用登记表> 其他费用 = sqlManager.execute(new SQLReady("select 金额,count(*) as count,sum(金额) as 总金额 from 宣传员费用登记表 where 领取人 = ? and (金额 !=30 and 金额!=40 and 金额!=50) and 日期 between ? and ? group by 金额 ", new Object[] {name,dateRangeStart, dateRangeEnd}),宣传员费用登记表.class);
                        if (其他费用 != null && 其他费用.size() == 1) {
                            model.put("qitafeiyong",其他费用.get(0));
                            if (其他费用.get(0).get总金额() != null) {
                                qitafeiyong = 其他费用.get(0).get总金额();
                            }
                        } else if (其他费用.size() > 1) {
                            File f = new File(root + "\\宣传员\\error.txt");
                            FileUtils.write(f,name + "其他费用|",true);
                        }


                        model.put("qitafeiyongheji",String.valueOf(jianyiqi + chaobannian + buchongjieshaofei + qitafeiyong));

                    } catch (IOException e) {
                        e.printStackTrace();
                    }

                    System.out.println("----------------");
                    String excelTemplate = "excelTemplates/管理费费用统计.xls";
                    try(InputStream is = Thread.currentThread().getContextClassLoader().getResourceAsStream(excelTemplate)) {
                        if(is==null) {
                            throw new PlatformException("模板资源不存在："+excelTemplate);
                        }
                        File f = new File(root + "\\宣传员\\" + name + ".xls");
                        f.getParentFile().mkdirs();

                        FileOutputStream os = new FileOutputStream(f);

                        model.put("lufeiList", lufeiList);
                        model.put("name",name);
                        model.put("nowdate", new Date());

                        JxlsUtils.exportExcel(is,os,model);
                    } catch (IOException e) {
                        throw new PlatformException(e.getMessage());
                    }

                    xcyHuizong.put("name",name);
                    xcyHuizong.put("jianyiqi",jianyiqi);jianyiqiTotal = jianyiqiTotal + jianyiqi;
                    xcyHuizong.put("chaobannian",chaobannian);chaobannianTotal = chaobannianTotal + chaobannian;
                    xcyHuizong.put("buchongjieshaofei",buchongjieshaofei);buchongjieshaofeiTotal = buchongjieshaofeiTotal + buchongjieshaofei;
                    xcyHuizong.put("qitafeiyong",qitafeiyong);qitafeiyongTotal = qitafeiyongTotal + qitafeiyong;
                    xcyHuizong.put("lufeiheji",lufeiheji);lufeihejiTotal = lufeihejiTotal + lufeiheji;

                    System.out.println(name);
                    xcyHuizong.put("heji",jianyiqi + chaobannian + buchongjieshaofei + lufeiheji + qitafeiyong);
                    xcyHuizongList.add(xcyHuizong);
                }
            }
            String excelTemplate = "excelTemplates/管理费费用汇总.xls";
            try(InputStream is = Thread.currentThread().getContextClassLoader().getResourceAsStream(excelTemplate)) {
                if(is==null) {
                    throw new PlatformException("模板资源不存在："+excelTemplate);
                }
                File f = new File(root + "\\宣传员\\1-汇总" + xcyList.size() + ".xls");
                f.getParentFile().mkdirs();

                FileOutputStream os = new FileOutputStream(f);
                Map<String, Object> model = new HashMap<>();
                model.put("jianyiqiTotal", jianyiqiTotal);
                model.put("chaobannianTotal", chaobannianTotal);
                model.put("buchongjieshaofeiTotal", buchongjieshaofeiTotal);
                model.put("lufeihejiTotal", lufeihejiTotal);
                model.put("qitafeiyongTotal", qitafeiyongTotal);

                model.put("xcyHuizongList", xcyHuizongList);
                model.put("nowdate", new Date());
                model.put("dateRange",dateRange);
                JxlsUtils.exportExcel(is,os,model);
            } catch (IOException e) {
                throw new PlatformException(e.getMessage());
            }
        }
    }
}
